<?php
/*
PHP REST SQL: A HTTP REST interface to relational databases
written in PHP

postgresql.php :: PostgreSQL database adapter
Copyright (C) 2008 Guido De Rosa <guidoderosa@gmail.com>

based on MySQL driver mysql.php by Paul James
Copyright (C) 2004 Paul James <paul@peej.co.uk>

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/

/* $id$ */

require_once dirname(__FILE__) . '/Exception.class.php';

class DB_PostgreSQL extends DB
{
    /**
     * MySQLi构造函数
     *
     * @param array $dbInfo 数据库配置信息
     * @param string $dbKey db的key
     * @param int $fetchMode 返回的数据格式
     */
    public function __construct(&$dbInfo, $dbKey, $fetchMode)
    {
        $this->dbKey = $dbKey;
        $this->dsn = &$dbInfo;
        $this->fecthMode = $fetchMode;
    }

    /**
     * 连接数据库
     *
     * 连接数据库之前可能需要改变DSN，一般不建议使用此方法
     *
     * @param string $type 选择连接主服务器或者从服务器
     * @return boolean
     * @throws DB_Exception
     */
    public function connect($type = 'slave')
    {

        $connString = sprintf(
            'host=%s port=%s dbname=%s user=%s password=%s',
            $this->dsn['dbHost'],
            $this->dsn['dbPort'],
            $this->dsn['dbName'],
            $this->dsn['dbUser'],
            $this->dsn['dbPass']
        );

        if ($this->dbConn = pg_pconnect($connString)) {
            $this->uConn = $this->dbConn; //更新db
            $this->qConn = $this->dbConn; //查询db
            return TRUE;
        }
        throw new DB_Exception('数据库连接失败!');
    }

    /**
     * 关闭数据库连接
     *
     * 一般不需要调用此方法
     */
    public function close()
    {
        if ($this->uConn === $this->qConn) {
            if (is_object($this->uConn)) {
                pg_close($this->uConn);
            }
        } else {
            if (is_object($this->uConn)) {
                pg_close($this->uConn);
            }
            if (is_object($this->qConn)) {
                pg_close($this->qConn);
            }
        }
    }

    /**
     * 执行一个SQL查询
     *
     * 本函数仅限于执行SELECT类型的SQL语句
     *
     * @param string $sql SQL查询语句
     * @param mixed $limit 整型或者字符串类型，如10|10,10
     * @param boolean $quick 是否快速查询
     * @return resource 返回查询结果资源句柄
     * @throws DB_Exception
     */
    public function query($sql, $limit = null, $quick = false)
    {
        if ($limit != null) {
            if (!preg_match('/^\s*SHOW/i', $sql) && !preg_match('/FOR UPDATE\s*$/i', $sql) && !preg_match('/LOCK IN SHARE MODE\s*$/i', $sql)) {
                $sql = $sql . ' LIMIT ' . $limit;
            }
        }
        $this->sqls[] = $sql;
        $this->qSqls[] = $sql;
        $this->sql = $sql;
        $this->time[count($this->sqls) - 1][] = microtime(true);
        if (!$this->qConn) {
            $this->connect('slave');
        }
        if (!pg_ping($this->qConn)) {
            $this->connect();
        }
        $this->qrs = pg_query($this->qConn, $sql);
        if (!$this->qrs) {
            throw new DB_Exception('查询失败:' . pg_last_error($this->qConn));
        } else {
            $this->time[count($this->sqls) - 1][] = microtime(true);
            $this->queryNum++;
            return $this->qrs;
        }
    }

    /**
     * 获取结果集
     *
     * @param resource $rs 查询结果资源句柄
     * @param int $fetchMode 返回的数据格式
     * @return array 返回数据集每一行，并将$rs指针下移
     */
    public function fetch($rs, $fetchMode = self::DB_FETCH_DEFAULT)
    {
        switch ($fetchMode) {
            case 1 :
                $fetchMode = self::DB_FETCH_ASSOC;
                break;
            case 2 :
                $fetchMode = self::DB_FETCH_ROW;
                break;
            case 3 :
                $fetchMode = self::DB_FETCH_ARRAY;
                break;
            default :
                $fetchMode = self::DB_FETCH_DEFAULT;
                break;
        }
        return pg_fetch_array($rs, NULL, $fetchMode);
    }

    /**
     * 执行一个SQL更新
     *
     * 本方法仅限数据库UPDATE操作
     *
     * @param string $sql
     *            数据库更新SQL语句
     * @return boolean
     * @throws DB_Exception
     */
    public function update($sql)
    {
        $this->sql = $sql;
        $this->sqls[] = $this->sql;
        $this->uSqls[] = $this->sql;
        if (!$this->uConn) {
            $this->connect('master');
        }

        $this->urs = pg_query($this->uConn, $sql);

        if (!$this->urs) {
            throw new DB_Exception('更新失败:' . mysqli_error($this->uConn), mysqli_errno($this->qConn));
        } else {
            $this->updateNum++;
            return $this->urs;
        }
    }

    /**
     * 返回SQL语句执行结果集中的第一行第一列数据
     *
     * @param string $sql 需要执行的SQL语句
     * @return mixed 查询结果
     * @throws DB_Exception
     */
    public function getOne($sql)
    {
        if (!$rs = $this->query($sql, 1, true)) {
            return 0;
        }
        $row = $this->fetch($rs, self::DB_FETCH_ROW);
        $this->free($rs);
        return $row[0];
    }

    /**
     * 返回SQL语句执行结果集中的第一列数据
     *
     * @param string $sql 需要执行的SQL语句
     * @param mixed $limit 整型或者字符串类型，如10|10,10
     * @return bool|array 结果集数组
     * @throws DB_Exception
     */
    public function getCol($sql, $limit = null)
    {
        if (!$rs = $this->query($sql, $limit, true)) {
            return false;
        }
        $result = array();
        while ($rows = $this->fetch($rs, self::DB_FETCH_ROW)) {
            $result[] = $rows[0];
        }
        $this->free($rs);
        return $result;
    }

    /**
     * 返回SQL语句执行结果中的第一行数据
     *
     * @param string $sql 需要执行的SQL语句
     * @param int $fetchMode 返回的数据格式
     * @return bool|array 结果集数组
     * @throws DB_Exception
     */
    public function getRow($sql, $fetchMode = self::DB_FETCH_DEFAULT)
    {
        if (!$rs = $this->query($sql, 1, true)) {
            return false;
        }
        $row = $this->fetch($rs, $fetchMode);
        $this->free($rs);
        return $row;
    }

    /**
     * 返回SQL语句执行结果中的所有行数据
     *
     * @param string $sql 需要执行的SQL语句
     * @param mixed $limit 整型或者字符串类型，如10|10,10
     * @param int $fetchMode 返回的数据格式
     * @return bool|array 结果集二维数组
     * @throws DB_Exception
     */
    public function getAll($sql, $limit = null, $fetchMode = self::DB_FETCH_DEFAULT)
    {
        if (!$rs = $this->query($sql, $limit, true)) {
            return false;
        }
        $allRows = array();
        while ($row = $this->fetch($rs, $fetchMode)) {
            $allRows[] = $row;
        }
        $this->free($rs);
        return $allRows;
    }

    /**
     * 返回上次查询受影响的条目数
     *
     * @param $res
     * @return int
     */
    public function affectRows($res)
    {
        return pg_affected_rows(empty($res) ? $this->uConn : $res);
    }

    /**
     * 转义需要插入或者更新的字段值
     *
     * 在所有查询和更新的字段变量都需要调用此方法处理数据
     *
     * @param mixed $str 需要处理的变量
     * @return mixed 返回转义后的结果
     */
    public function escape($str)
    {
        if (is_array($str)) {
            foreach ($str as $key => $value) {
                $str[$key] = $this->escape($value);
            }
        } else {
            return addslashes($str);
        }
        return $str;
    }

    /**
     * 释放当前查询结果资源句柄
     * @param $rs
     * @return bool
     */
    public function free($rs)
    {
        if ($rs) {
            return pg_free_result($rs);
        }
    }

    /**
     * 析构函数，暂时不需要做什么处理
     */
    public function __destruct()
    {
        $this->close();
    }
}


/**
 * PHP REST PostgreSQL class
 * PostgreSQL connection class.
 */
class DB_PostgreSQL2
{

    /**
     * @var int
     */
    var $lastInsertPKeys;

    /**
     * @var resource
     */
    var $lastQueryResultResource;

    /**
     * @var resource Database resource
     */
    var $db;

    /**
     * MySQLi构造函数
     *
     * @param array $dbInfo 数据库配置信息
     * @param string $dbKey db的key
     * @param int $fetchMode 返回的数据格式
     */
    public function __construct(&$dbInfo, $dbKey, $fetchMode)
    {
        $this->dbKey = $dbKey;
        $this->dsn = &$dbInfo;
        $this->fecthMode = $fetchMode;
        $this->connect($dbInfo);
    }

    /**
     * Connect to the database.
     * @param array config
     * @return bool
     */
    function connect($config)
    {

        $connString = sprintf(
            'host=%s port=%s dbname=%s user=%s password=%s',
            $config['dbHost'],
            $config['dbPort'],
            $config['dbName'],
            $config['dbUser'],
            $config['dbPass']
        );

        if ($this->db = pg_pconnect($connString)) {
            return TRUE;
        }
        return FALSE;
    }

    /**
     * Close the database connection.
     */
    function close()
    {
        pg_close($this->db);
    }

    /**
     * Get the columns in a table.
     * @param string $table
     * @return resource A resultset resource
     */
    function getColumns($table)
    {
        $qs = sprintf('SELECT * FROM information_schema.columns WHERE table_name =\'%s\'', $table);
        return pg_query($qs, $this->db);
    }

    /**
     * Get a row from a table.
     * @param string $table
     * @param string $where
     * @return resource A resultset resource
     */
    function getRow($table, $where)
    {
        $result = pg_query(sprintf('SELECT * FROM %s WHERE %s', $table, $where));
        if ($result) {
            $this->lastQueryResultResource = $result;
        }
        return $result;
    }

    /**
     * Get the rows in a table.
     * @param string primary The names of the primary columns to return
     * @param string $table
     * @return resource A resultset resource
     */
    function getTable($primary, $table)
    {
        $result = pg_query(sprintf('SELECT %s FROM %s', $primary, $table));
        if ($result) {
            $this->lastQueryResultResource = $result;
        }
        return $result;
    }

    /**
     * Get the tables in a database.
     * @return resource A resultset resource
     */
    function getDatabase()
    {
        return pg_query('SELECT table_name FROM information_schema.tables WHERE table_schema=\'public\'');
    }

    /**
     * Get the primary keys for the request table.
     * @param string $table
     * @return array The primary key field names
     */
    function getPrimaryKeys($table)
    {
        $i = 0;
        $primary = NULL;
        do {
            $query = sprintf('SELECT pg_attribute.attname
		        FROM pg_class, pg_attribute, pg_index
                WHERE pg_class.oid = pg_attribute.attrelid AND
                pg_class.oid = pg_index.indrelid AND
                pg_index.indkey[%d] = pg_attribute.attnum AND
                pg_index.indisprimary = \'t\'
                and relname=\'%s\'',
                $i,
                $table
            );
            $result = pg_query($query);
            $row = pg_fetch_assoc($result);
            if ($row) {
                $primary[] = $row['attname'];
            }
            $i++;
        } while ($row);

        return $primary;
    }

    /**
     * Update a row.
     * @param string $table
     * @param string $values
     * @param string $where
     * @return bool
     */
    function updateRow($table, $values, $where)
    {
        # translate from MySQL syntax :)
        $values = preg_replace('/"/', '\'', $values);
        $values = preg_replace('/`/', '"', $values);
        $qs = sprintf('UPDATE %s SET %s WHERE %s', $table, $values, $where);
        $result = pg_query($qs);
        if ($result) {
            $this->lastQueryResultResource = $result;
        }
        return $result;
    }

    /**
     * Insert a new row.
     * @param string $table
     * @param string $names
     * @param string $values
     * @return bool
     */
    function insertRow($table, $names, $values)
    {
        # translate from MySQL syntax
        $names = preg_replace('/`/', '"', $names); #backticks r so MySQL-ish! ;)
        $values = preg_replace('/"/', '\'', $values);
        $pkeys = join(', ', $this->getPrimaryKeys($table));

        $qs = sprintf(
            'INSERT INTO $table ("%s") VALUES ("%s") RETURNING (%s)',
            $names,
            $values,
            $pkeys
        );
        $result = pg_query($qs); #or die(pg_last_error());

        $lastInsertPKeys = pg_fetch_row($result);
        $this->lastInsertPKeys = $lastInsertPKeys;

        if ($result) {
            $this->lastQueryResultResource = $result;
        }
        return $result;
    }

    /**
     * Get the columns in a table.
     * @param $table
     * @param $where
     * @return resource A resultset resource
     */
    function deleteRow($table, $where)
    {
        $result = pg_query(sprintf('DELETE FROM %s WHERE %s', $table, $where));
        if ($result) {
            $this->lastQueryResultResource = $result;
        }
        return $result;
    }

    /**
     * Escape a string to be part of the database query.
     * @param string string The string to escape
     * @return string The escaped string
     */
    function escape($string)
    {
        return pg_escape_string($string);
    }

    /**
     * Fetch a row from a query resultset.
     * @param resource resource A resultset resource
     * @return array An array of the fields and values from the next row in the resultset
     */
    function row($resource)
    {
        return pg_fetch_assoc($resource);
    }

    /**
     * The number of rows in a resultset.
     * @param resource resource A resultset resource
     * @return int The number of rows
     */
    function numRows($resource)
    {
        return pg_num_rows($resource);
    }

    /**
     * The number of rows affected by a query.
     * @return int The number of rows
     */
    function numAffected()
    {
        return pg_affected_rows($this->lastQueryResultResource);
    }

    /**
     * Get the ID of the last inserted record.
     * @return int The last insert ID ('a/b' in case of multi-field primary key)
     */
    function lastInsertId()
    {
        return join('/', $this->lastInsertPKeys);
    }

}